package Databaus2;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Random;

/**
 * Database is a class that specifies the interface to the movie database. Uses
 * JDBC and the MySQL Connector/J driver.
 */
public class Database {
	/**
	 * The database connection.
	 */
	private Connection conn;

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}

	// srv016.dmz.hbg.lu.se
	// db14
	// mNZdrk8gKZ

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection(String userName, String password) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// conn = DriverManager.getConnection(
			// "jdbc:mysql://srv016.dmz.hbg.lu.se/" + userName, userName,
			// password);
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/databaus", userName, password);
		} catch (SQLException e) {
			System.err.println(e);
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			System.err.println(e);
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
		}
		conn = null;
		System.out.println("Connection to database lost");
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}

	public boolean producePallet(String cookieName, int k) {
		ArrayList<Material> materialList = new ArrayList<Material>();
		Statement stmt;
		ResultSet rs;
		String getIngredientQtySQL = "select * from ingredientqty where cookieName = '" + cookieName + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(getIngredientQtySQL);
			while(rs.next()){
				materialList.add(new Material(rs.getString("ingredientName"), rs.getInt("ingredientQty"), null, 0));
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		ArrayList<Material> storageList = new ArrayList<Material>();
		String getIngredientsSQL = "select * from ingredients where ingredientName = '" + materialList.get(0).getMaterialName() + "'";
		for(int i = 1; i < materialList.size(); i++){
			getIngredientsSQL += " or ingredientName = '" + materialList.get(i).getMaterialName() + "'";
		}
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(getIngredientsSQL);
			while (rs.next()) {
				storageList.add(new Material(rs.getString("ingredientName"),
						rs.getInt("storageAmount"), rs.getString("lastDelivery"), rs.getInt("deliveryAmount")));
			}
			String updateIngredientSQL;
			boolean enough = true;;
			for (int i = 0; i < storageList.size(); i++) {
				if(storageList.get(i).getAmount() >= materialList.get(i).getAmount()*k) {
					
				} else {
					enough = false;
				}
			}
			if(enough){
				for(int i = 0; i < storageList.size(); i++){
					updateIngredientSQL = "update ingredients set storageAmount = storageAmount - "
							+ materialList.get(i).getAmount()*k
							+ " where ingredientName = '"
							+ storageList.get(i).getMaterialName() + "'";
					stmt.executeUpdate(updateIngredientSQL);
				}
				for(int i = 0; i < k; i++){
					addPallet(cookieName);
				}
				
				stmt.close();
				return true;
			} else {
				stmt.close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean addOrder(String customerName, String deliveryDate) {
		String addOrderSQL = "insert into orders (customerName, deliveryDate, delivered) values('"
				+ customerName + "', '" + deliveryDate + "', " + "false" + ")";
		Statement stmt;
		try {
			stmt = conn.createStatement();
			stmt.execute(addOrderSQL);
			ResultSet rs = stmt.executeQuery("select MAX(oID) from orders");
			while(rs.next()){
				randomPallets(rs.getInt("MAX(oID)"));
			}	
			stmt.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	//
	private boolean randomPallets(int oID){
		String randomPalletsSQL = "select * from pallets where oID is NULL";
		Statement stmt, stmt2;
		int lastoID = oID;
		int temp;
		try {
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			ResultSet rs = stmt.executeQuery(randomPalletsSQL);
			while(rs.next()){
				temp = rs.getInt("pID");
				stmt2.execute("update pallets set oID = "+lastoID+" where pID = "+temp);
				lastoID--;
			}
			stmt.close();
			stmt2.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean addPallet(String cookieName) {
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
		String date = dateFormat.format(cal.getTime());
		String time = timeFormat.format(cal.getTime());
		String addPalletSQL = "insert into pallets (cookieName, bakingDate, bakingTime) values('" + cookieName	+ "', '" + date	+ "', '" + time	+ "')";
		try {
			Statement stmt = conn.createStatement();
			if (stmt.execute(addPalletSQL)) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean addIngredient(String ingredient, int amount){
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		String date = dateFormat.format(cal.getTime());
		String sql1 = "update ingredients set storageAmount = storageAmount + " + amount + " where ingredientName = '" + ingredient + "'";
		String sql2 = "update ingredients set deliveryAmount = " + amount + " where ingredientName = '" + ingredient + "'";
		String sql3 = "update ingredients set lastDelivery = '" + date + "' where ingredientName = '" + ingredient + "'";
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(sql1); 
			stmt.execute(sql2); 
			stmt.execute(sql3); 
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public ArrayList<Customer> getCustomers() {
		ArrayList<Customer> customerList = new ArrayList<Customer>();
		Customer customer;
		String getCustomersSQL = "select * from Customers";
		String getOrdersSQL;
		try {
			Statement stmt1 = conn.createStatement();
			Statement stmt2 = conn.createStatement();
			ResultSet rs = stmt1.executeQuery(getCustomersSQL);
			while (rs.next()) {
				getOrdersSQL = "select count(*) from Orders where customerName='"
						+ rs.getString("customerName") + "'";
				ResultSet orders = stmt2.executeQuery(getOrdersSQL);
				while (orders.next()) {
					customer = new Customer(rs.getString("customerName"),
							rs.getString("address"), orders.getInt(1));
					customerList.add(customer);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customerList;
	}

	public ArrayList<Pallet> getPallets() {
		ArrayList<Pallet> palletList = new ArrayList<Pallet>();
		Pallet pallet;
		String getPalletsSQL = "select * from Pallets";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getPalletsSQL);
			while (rs.next()) {
				pallet = new Pallet(rs.getInt("pID"),
						rs.getString("cookieName"), rs.getBoolean("blocked"),
						rs.getString("bakingDate"), rs.getString("bakingTime"),
						rs.getInt("oID"), rs.getString("location"));
				palletList.add(pallet);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return palletList;
	}

	public boolean blockPallet(int pID) {
		String blockPalletSQL = "update pallets set blocked = 1 where pID = "
				+ pID;
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(blockPalletSQL);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean unblockPallet(int pID) {
		String unblockPalletSQL = "update pallets set blocked = 0 where pID = "
				+ pID;
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(unblockPalletSQL);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public String[] getPalletInfo(int oID) {
		String getPalletInfoSQL = "select * from orders where oID=" + oID;
		String[] temp = new String[3];
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getPalletInfoSQL);

			while (rs.next()) {
				temp[0] = rs.getString("deliveryDate");
				temp[1] = rs.getBoolean("delivered") ? "Yes" : "No";
				temp[2] = rs.getString("customerName");
			}
			if (temp[2] == null) {
				return new String[] { "Unknown", "Unknown", "Unknown" };
			} else {
				return temp;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return new String[] { "Unknown", "Unknown", "Unknown" };
		}

	}

	public ArrayList<Pallet> search(String cookieName, String date1,
			String date2, int pID, int oID, String blocked, String customer) {
		ArrayList<Pallet> palletList = new ArrayList<Pallet>();
		//String searchSQL = "select * from pallets natural join orders where ";
		String searchSQL = "select * from pallets left outer join orders on pallets.oID = orders.oID  where ";
		if (!cookieName.equals("")) {
			searchSQL += "cookieName = '" + cookieName + "' and ";
		}
		if (!date1.equals("") && !date2.equals("")) {
			searchSQL += "bakingDate between '" + date1 + "' and '" + date2
					+ "' and ";
		} else if (!date1.equals("")) {
			searchSQL += "bakingDate = '" + date1 + "' and ";
		} else if (!date2.equals("")) {
			searchSQL += "bakingDate = '" + date2 + "' and ";
		}
		if (pID != 0) {
			searchSQL += "pID = " + pID + " and ";
		}
		if (oID != 0) {
			searchSQL += "pallets.oID = " + oID + " and ";
		}
		if (!customer.equals("")) {
			searchSQL += "customerName = '" + customer + "' and ";
		}
		if (blocked.equals("true")) {
			searchSQL += "blocked = " + blocked + " and ";
		} else if (blocked.equals("false")) {
			searchSQL += "blocked = " + blocked + " and ";
		}
		searchSQL += "1=1";
		Statement stmt;
		try {
			System.out.println(searchSQL);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(searchSQL);
			while (rs.next()) {
				palletList.add(new Pallet(rs.getInt("pID"), rs
						.getString("cookieName"), rs.getBoolean("blocked"), rs
						.getString("bakingDate"), rs.getString("bakingTime"),
						rs.getInt("oID"), rs.getString("location")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return palletList;
	}

	public String[] getCookieList() {
		String getCookiesSQL = "select * from cookies";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getCookiesSQL);
			rs.last();
			String[] cookieList = new String[rs.getRow()];
			rs.beforeFirst();
			while (rs.next()) {
				cookieList[rs.getRow() - 1] = rs.getString("cookieName");
			}
			return cookieList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Order getOrder(int oID) {
		Order order = null;
		String getOrderSQL = "select * from orders where oID = " + oID;
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getOrderSQL);
			while (rs.next()) {
				order = new Order(rs.getInt("oID"),
						rs.getString("deliveryDate"),
						rs.getBoolean("delivered"),
						rs.getString("customerName"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return order;
	}

	public ArrayList<Material> getMaterials() {
		ArrayList<Material> materialList = new ArrayList<Material>();
		Material mat;
		String getPalletsSQL = "select * from ingredients";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getPalletsSQL);
			while (rs.next()) {
				mat = new Material(rs.getString("ingredientName"),
						rs.getInt("storageAmount"),
						rs.getString("lastDelivery"),
						rs.getInt("deliveryAmount"));
				materialList.add(mat);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return materialList;
	}
	
	public String[] getMaterialList() {
		String getCookiesSQL = "select * from ingredients";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getCookiesSQL);
			rs.last();
			String[] cookieList = new String[rs.getRow()];
			rs.beforeFirst();
			while (rs.next()) {
				cookieList[rs.getRow() - 1] = rs.getString("ingredientName");
			}
			return cookieList;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public void randomOrders(){
		Calendar cal = Calendar.getInstance();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		String date = dateFormat.format(cal.getTime());
		Random rand = new Random();
		ArrayList<Customer> customerList = new ArrayList<Customer>();
		String customer;
		customerList = getCustomers();
		//for(int i = 0; i < 5; i++){
			customer = customerList.get(rand.nextInt(customerList.size())).getName();
			addOrder(customer, date);
			System.out.println(customer);
			
	//	}
	}
}